/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package lichbaogiang;

import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.FileOutputStream;
import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import java.sql.*;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 *
 * @author TV
 */
public class Lichbaogiang extends JFrame implements ActionListener{

    /**
     * @param args the command line arguments
     */
    public static int size=0;
    JFrame fr = new JFrame();
    JPanel pn;
    JMenuBar menuBar;
    JToolBar toolBar;
    JMenu mFile, mEdit, mHelp;
    JMenuItem mShow, mUpdate, mDelete, mInfor, mSupport, mExit, mTeacher, mFind, mSubject, mExport;
    JButton but;
    JTable table = new JTable();
    ActionListener lst;
    DefaultTableModel model = new DefaultTableModel();
    
    String columNames[] = new String[20]; // Khởi tạo tên cho các cột của bảng
    //String[] columNames = {"MSGV","Ten","SDT","Email","NamBatDau","HSThamNien","HSChuyenMon","HSNCKH","Diem"};
    //String[][] data = new String[100][100];     // Dữ liệu cho bảng
    String[] data = new String[11];
    String[] rows = new String[20];
    JButton butTeacher;
    JTextField tim;
    public int j;
    public  String url = "jdbc:odbc:CNPM";
    Statement stm;
    ResultSet rs;
    Connection con;
    public String str1="";
    public String str2="";
    public String str3="";
    public String str4="";
    public String str5="";
    public String str6="";
    public String str7="";
    public String str8="";
    public String str9="";
    public String str10="";
    
    JButton butShow;
    JButton butSubject;
    JButton butExport;
    JButton butUpdate;
    JButton butDelete;
    JButton butFind;
    JButton butHelp;
    JButton butSupport;
    ImagePanel image;
    GetDataTeacher dtteacher;
    
    public Lichbaogiang()
    {
        gui();
        dtteacher = new GetDataTeacher();
        for(int k=0;k<dtteacher.count_row;k++)
        {
            System.out.println(k+" "+dtteacher.dsgv[k][1]);
        }
        image = new ImagePanel();
        pn.add(image, BorderLayout.CENTER);
        //pn.add(new JScrollPane(table),BorderLayout.CENTER);
        fr.add(pn);
        
        fr.setSize(1000,600);
        fr.setVisible(true);
        fr.setTitle("LỊCH BÁO GIẢNG");
        fr.setDefaultCloseOperation(EXIT_ON_CLOSE);
    }
    
    /*********************************
     * Tạo giao diện chương trình chính
     ********************************/
    public void gui()
    {
        // khoi tao Menu
        menuBar = new JMenuBar(); menuBar.setBackground(Color.lightGray);
        toolBar = new JToolBar(); toolBar.setEnabled(false);
        mFile = new JMenu("File");
        mEdit = new JMenu("Edit");
        mHelp = new JMenu("Help");
        
        
        /*
            Tao ToolBar
        */
        //Menu Show - hien thi lich bao giang
        ImageIcon showIcon = new ImageIcon(getClass().getResource("icon/Show.gif"));
        mShow = new JMenuItem("Lịch Báo Giảng",showIcon);
	mShow.addActionListener(this); 
	mFile.add(mShow); 
	butShow = new JButton(showIcon); 
	butShow.setToolTipText("Lịch Báo Giảng");  // but = mShow -> viet action chung
	butShow.addActionListener(this); 
	toolBar.add(butShow); 
        
        //Menu hiện Bảng Giáo viên
        ImageIcon teacherIcon = new ImageIcon(getClass().getResource("icon/teacher.gif"));
        mTeacher = new JMenuItem("Bảng Giáo Viên",teacherIcon);
	mTeacher.addActionListener(this); 
	mFile.add(mTeacher); 
	butTeacher = new JButton(teacherIcon); 
	butTeacher.setToolTipText("Bảng Giáo Viên");  // but = mShow -> viet action chung
	butTeacher.addActionListener(this); 
	toolBar.add(butTeacher); 
        
        //Menu hiện Bảng Môn Học
        ImageIcon subIcon = new ImageIcon(getClass().getResource("icon/monhoc.gif"));
        mSubject = new JMenuItem("Môn Học",subIcon);
	mSubject.addActionListener(this); 
	mFile.add(mSubject); 
	butSubject = new JButton(subIcon); 
	butSubject.setToolTipText("Môn Học");  // but = mShow -> viet action chung
	butSubject.addActionListener(this); 
	toolBar.add(butSubject); 
        
        //Menu Xuất ra file Excel
        ImageIcon expIcon = new ImageIcon(getClass().getResource("icon/export.gif"));
        mExport = new JMenuItem("Xuất Ra Excel",expIcon);
	mExport.addActionListener(this); 
	mFile.add(mExport); 
	butExport = new JButton(expIcon); 
	butExport.setToolTipText("Xuất Ra Excel");  // but = mShow -> viet action chung
	butExport.addActionListener(this); 
	toolBar.add(butExport); 
        mExport.setEnabled(false);
        butExport.setEnabled(false);
        toolBar.addSeparator(); toolBar.addSeparator(); // chia nhóm, tạo khoảng cách giữa 2 nhóm
        
        //Menu cap nhat / Update
        ImageIcon insertIcon = new ImageIcon(getClass().getResource("icon/Update.gif"));
        mUpdate = new JMenuItem("Cập nhật",insertIcon);
	mUpdate.addActionListener(this); 
	mEdit.add(mUpdate); 
	butUpdate = new JButton(insertIcon); 
	butUpdate.setToolTipText("Cập nhật");  // but = mUpdate -> viet action chung
	butUpdate.addActionListener(this); 
        mUpdate.setEnabled(false);
        butUpdate.setEnabled(false);
	toolBar.add(butUpdate); 
        
        //Menu Xóa
        ImageIcon delIcon = new ImageIcon(getClass().getResource("icon/Delete.gif"));
        mDelete = new JMenuItem("Xóa",delIcon);
	mDelete.addActionListener(this); 
	mEdit.add(mDelete); 
	butDelete = new JButton(delIcon); 
	butDelete.setToolTipText("Xóa");  // but = mDelete -> viet action chung
	butDelete.addActionListener(this); 
	toolBar.add(butDelete); toolBar.addSeparator();
        
        //Menu Tìm kiếm
        tim = new JTextField();
        tim.setFont(new Font("Times New Roman",1,20));
        //tim.setPreferredSize(new Dimension(10, 40));
        ImageIcon findIcon = new ImageIcon(getClass().getResource("icon/find.gif"));
        mFind = new JMenuItem("Tìm Kiếm",findIcon);
	mFind.addActionListener(this); 
	mEdit.add(mFind); 
	butFind = new JButton(findIcon); 
	butFind.setToolTipText("Tìm kiếm");  
	butFind.addActionListener(this); 
	toolBar.add(tim);
        toolBar.add(butFind); 
        toolBar.addSeparator(); toolBar.addSeparator();
        
        //Menu Thông tin
        ImageIcon infIcon = new ImageIcon(getClass().getResource("icon/Infor.gif"));
        mInfor = new JMenuItem("Thông Tin",infIcon);
	mInfor.addActionListener(this); 
	mHelp.add(mInfor); 
	butHelp = new JButton(infIcon); 
	butHelp.setToolTipText("Thông tin");  // but = mInfor -> viet action chung
	butHelp.addActionListener(this); 
	toolBar.add(butHelp); 
        
        //Menu giúp đỡ
        ImageIcon helpIcon = new ImageIcon(getClass().getResource("icon/Help.gif"));
        mSupport = new JMenuItem("Trợ giúp",helpIcon);
	mSupport.addActionListener(this); 
	mHelp.add(mSupport); 
	butSupport = new JButton(helpIcon); 
	butSupport.setToolTipText("Trợ giúp");  // but = mSupport -> viet action chung
	butSupport.addActionListener(this); 
	toolBar.add(butSupport); 
        toolBar.addSeparator(); toolBar.addSeparator(); // 
        
        //Menu thoat Exit
        ImageIcon exitIcon = new ImageIcon(getClass().getResource("icon/Exit.gif"));
        mExit = new JMenuItem("Thoát",exitIcon);
        lst = new ActionListener(){ 
	   public void actionPerformed(ActionEvent e){ 
               System.exit(0);
	   }  
	  };  
	mExit.addActionListener(lst); 
	mFile.add(mExit); 
	but = new JButton(exitIcon); 
	but.setToolTipText("Thoát");  // but = mExit -> viet action chung
	but.addActionListener(lst); 
	toolBar.add(but); 
        
        menuBar.add(mFile);
        menuBar.add(mEdit);
        menuBar.add(mHelp);
        fr.setJMenuBar(menuBar);
        //fr.add(toolBar,BorderLayout.NORTH);
        pn = new JPanel(); 
        pn.setLayout(new BorderLayout(20,20));
        pn.add(toolBar,BorderLayout.NORTH);
        pn.setBackground(Color.green);
    }
    /*******************************
     * ** Chon chon chia mon hoc
     * *****************************/
     public int auto(String str)
     {
        boolean t=true;
        int i = 0;
        int n=0;
        if(str.equals("Chuyen de 3")||str.equals("Th Chuyen de 3")) n=17;
        else if(str.equals("Chuyen de 1")||str.equals("TH Chuyen de 1")) n=5;
        else if(str.equals("Do hoa may tinh")) n=7;
        else if(str.equals("Lap trinh ham & logic"))n=0;
        else if(str.equals("TH Cau truc may tinh"))n=23;
        else if(str.equals("Th Do hoa may tinh")||str.equals("Th Lap trinh java")||str.equals("Th Lap trinh mang")||str.equals("TH Chuyen de 1")) n=13;
        else if(str.equals("TH Lap trinh ham Logic")||str.equals("TH Chuong trinh dich"))n=19;
        
        else n= (int) (Math.random()*dtteacher.count_row);
        return n;
     }
    /*******************************
     * chia GVCN
     *******************************/
    public int CNL()
    {
        boolean t=true;
        int i = 0;
        int n= (int) (Math.random()*dtteacher.count_row);
        while(t)
        {
        float diem = Float.parseFloat(dtteacher.dsgv[n][2]);
        //if((str.equals("CNL"))) 
            n= (int) (Math.random()*dtteacher.count_row);
        if(diem<3) t=false;
        }
        return n;
    }
    /*******************************
     * Kết nối cơ sở dữ liệu
     ******************************/
    public void LoadCSDL(String sql)
    {
        String colum[] = {"STT","TENHOCPHAN","TC","TH","LT","DA","TT","LOP","MALOPHOCPHAN", "TenGV"}; 
        columNames = colum;
        model.setColumnIdentifiers(columNames);
	table.setModel(model);
        Statement stm;
        ResultSet rs;
        Connection con;
        try{
        	
        	con = DriverManager.getConnection(url);
        	stm = con.createStatement();
                rs = stm.executeQuery(sql);
                ResultSetMetaData rsmd = rs.getMetaData();
                int numberOfColumns = rsmd.getColumnCount();
                while(rs.next())
        	{
                    data[0]=rs.getString(1);
                    data[1]=rs.getString(2);
                    data[2]=rs.getString(3);
                    data[3]=rs.getString(4);
                    data[4]=rs.getString(5);
                    data[5]=rs.getString(6);
                    data[6]=rs.getString(7);
                    data[7]=rs.getString(8);
                    data[8]=rs.getString(9);
                    if(data[1].equals("Chu nhiem lop"))
                    { 
                        data[9]=dtteacher.dsgv[CNL()][1];
                        
                    }
                    else 
                    {
                        data[9]=dtteacher.dsgv[auto(data[1])][1];
                       
                    }
                    model.addRow(data);
        	}  	
        }catch(Exception e){
        	//System.out.println("Error System! " + e);
        }
        
    }
    
    /*********************************************
     * Hàm Khởi động
     ********************************************/
    
    public static void main(String[] args) {
        // TODO code application logic here
        
        new Lichbaogiang();
        //new GiangVien().dispose();
    }

    
    /**********************************************
     * Hàm xử lý sự kiện
     *********************************************/
    public void actionPerformed(ActionEvent e) {
        
        int i = 0, j = 0;
        
        if(e.getSource() == mTeacher || e.getSource() == butTeacher)
        {
            new GiangVien();
        }
        
        if(e.getSource() == mShow || e.getSource() == butShow)
        {
            LoadCSDL("Select * from DSPC");
            
            image.hide();
            pn.add(new JScrollPane(table),BorderLayout.CENTER);
            
            mShow.setEnabled(false);
            butShow.setEnabled(false);
            mUpdate.setEnabled(true);
            butUpdate.setEnabled(true);
            mExport.setEnabled(true);
            butExport.setEnabled(true);
        }
        
        if(e.getSource() == mSubject || e.getSource() == butSubject)
        {
            new MonHoc();
        }
        //Funtion seach
        if(e.getSource() == mFind||e.getSource() == butFind)
        {   
            String data[] = new String[20];
            
            String chuoi = tim.getText();
            System.out.println(chuoi);
            String kq="";
            String sql= "SELECT * FROM LICHHOC WHERE Lop='"+chuoi+"' OR TenGV='"+chuoi+"'";
            Properties tc = new Properties(); 
            tc.put("charSet", "UTF-8");
            try {
                con = DriverManager.getConnection(url);
                Statement stmt = con.createStatement();
                rs = stmt.executeQuery(sql);
                ResultSetMetaData rsmd = rs.getMetaData();
                int numCol = rsmd.getColumnCount();
                while(rs.next())
                {
                     for(j = 1;j <= numCol; j++)
        		data[j-1] = (rs.getString(j)); 
                 }
            } catch (SQLException ex) {
                Logger.getLogger(Lichbaogiang.class.getName()).log(Level.SEVERE, null, ex);
            }
            for(j=0;j<10;j++)
            {
                kq = kq+"   "+data[j];
            }
            JOptionPane.showConfirmDialog(this,kq,"Thông báo",JOptionPane.YES_OPTION);
        }
        if(e.getSource()==mUpdate||e.getSource()==butUpdate)
        {
            if(JOptionPane.showConfirmDialog(null,"Bạn muốn lưu lại không?","Thông báo",JOptionPane.YES_NO_OPTION)==0) 
            {
                String sql="";
                int kt= model.getRowCount();
                for(j=0;j<kt;j++)
                {
                    str1=(String) model.getValueAt(j, 0);
                    str2=(String) model.getValueAt(j, 1);
                    str3=(String) model.getValueAt(j, 2);
                    str4=(String) model.getValueAt(j, 3);
                    str5=(String) model.getValueAt(j, 4);
                    str6=(String) model.getValueAt(j, 5);
                    str7=(String) model.getValueAt(j, 6);
                    str8=(String) model.getValueAt(j, 7);
                    str9=(String) model.getValueAt(j, 8);
                    str10=(String)model.getValueAt(j, 9);
                    sql="UPDATE LICHHOC SET TenGV='"+str10+"' WHERE STT='"+str1+"'";
                /*sql="INSERT INTO LICHHOC(STT,TenHocPhan,TC,LT,TH,DA,TT,Lop,MaLopHP,TenGV) VALUES('"+str1+"','"
                        +str2+"','"+str3+"','"+str4+"','"
                        +str5+"','"+str6+"','"+str7+"','"
                        +str8+"','"+str9+"','"+str10+"')";*/
                    System.out.println(sql);
                    try {
                        con = DriverManager.getConnection(url);
                        stm = con.createStatement();
                        stm.executeUpdate(sql);
                    } catch (SQLException ex) {
                    //Logger.getLogger(Lichbaogiang.class.getName()).log(Level.SEVERE, null, ex);
                }   
        	}
        }  
            }
        if(e.getSource()==mInfor||e.getSource()==butHelp)
        {
             JOptionPane.showConfirmDialog(this,"Chương trình phân chia lich giảng day do nhóm sinh viên khoa Công nghệ thông tin thực hiện"
                     + "","Thông tin",JOptionPane.CLOSED_OPTION);
        }
        if(e.getSource()==mHelp||e.getSource()==butSupport)
        {
            JOptionPane.showConfirmDialog(this,"Hãy liên hệ với chúng tôi khi xảy ra sự cố"
                     + "","Thông tin",JOptionPane.CLOSED_OPTION);
        }
        if(e.getSource() == mExport || e.getSource() == butExport)
        {
            String path = null;
            JFileChooser showSave = new JFileChooser();
            int useChoose = showSave.showSaveDialog(fr);
            if(useChoose == JFileChooser.APPROVE_OPTION)
            {
                path = showSave.getSelectedFile() + ".xls";
            }
            try{
                //String filename = "D:/data.xls" ;
                String filename = path;
                HSSFWorkbook hwb = new HSSFWorkbook();
                HSSFSheet sheet =  hwb.createSheet("Lịch Báo Giảng");

                HSSFRow rowhead = sheet.createRow((short)0);
                rowhead.createCell((short) 0).setCellValue("STT");
                rowhead.createCell((short) 1).setCellValue("Tên HP");
                rowhead.createCell((short) 2).setCellValue("Tín chỉ");
                rowhead.createCell((short) 3).setCellValue("Lý thuyết");
                rowhead.createCell((short) 4).setCellValue("Thực hành");
                rowhead.createCell((short) 5).setCellValue("Đồ Án");
                rowhead.createCell((short) 6).setCellValue("Thực tập");
                rowhead.createCell((short) 7).setCellValue("Lớp");
                rowhead.createCell((short) 8).setCellValue("Mã Lớp HP");
                rowhead.createCell((short) 9).setCellValue("Tên Giảng viên");

                /*Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                String rl = "jdbc:odbc:CNPM";
                Connection con = DriverManager.getConnection(rl);
                Statement st=con.createStatement();
                ResultSet rs=st.executeQuery("Select * from LICHHOC");
                int n = 1;
                while(rs.next()){
                    HSSFRow row = sheet.createRow((short)n);
                    row.createCell((short) 0).setCellValue(Integer.parseInt(rs.getString(1))); //STT
                    row.createCell((short) 1).setCellValue(rs.getString(2)); //TenLopHP
                    row.createCell((short) 2).setCellValue(Integer.parseInt(rs.getString(3))); //TC
                    row.createCell((short) 3).setCellValue(Integer.parseInt(rs.getString(4))); //LT
                    row.createCell((short) 4).setCellValue(Integer.parseInt(rs.getString(5))); //TH
                    row.createCell((short) 5).setCellValue(Integer.parseInt(rs.getString(6))); //DA
                    row.createCell((short) 6).setCellValue(Integer.parseInt(rs.getString(7))); //TT
                    row.createCell((short) 7).setCellValue(rs.getString(8)); //Lop
                    row.createCell((short) 8).setCellValue(rs.getString(9)); //MaLopHP
                    row.createCell((short) 9).setCellValue(rs.getString(10)); //TenGV
                    n++;
                }*/
                int kt = model.getRowCount();
                for(j = 0; j < kt; j++)
                {
                    HSSFRow row = sheet.createRow((short)j+1);
                    str1=(String) model.getValueAt(j, 0); row.createCell((short) 0).setCellValue(Integer.parseInt(str1)); //STT
                    str2=(String) model.getValueAt(j, 1); row.createCell((short) 1).setCellValue(str2); //TenLopHP
                    str3=(String) model.getValueAt(j, 2); row.createCell((short) 2).setCellValue(Integer.parseInt(str3)); //TC
                    str4=(String) model.getValueAt(j, 3); row.createCell((short) 3).setCellValue(Integer.parseInt(str4)); //LT
                    str5=(String) model.getValueAt(j, 4); row.createCell((short) 4).setCellValue(Integer.parseInt(str5)); //TH
                    str6=(String) model.getValueAt(j, 5); row.createCell((short) 5).setCellValue(Integer.parseInt(str6)); //DA
                    str7=(String) model.getValueAt(j, 6); row.createCell((short) 6).setCellValue(Integer.parseInt(str7)); //TT
                    str8=(String) model.getValueAt(j, 7); row.createCell((short) 7).setCellValue(str8); //Lop
                    str9=(String) model.getValueAt(j, 8); row.createCell((short) 8).setCellValue(str9); //MaLopHP
                    str10=(String)model.getValueAt(j, 9); row.createCell((short) 9).setCellValue(str10); //TenGV
                }
                FileOutputStream fileOut =  new FileOutputStream(filename);
                hwb.write(fileOut);
                fileOut.close();
                System.out.println("Your excel file has been generated!");
                JOptionPane.showMessageDialog(fr, "Xuất File thành công!","Thông báo!",JOptionPane.DEFAULT_OPTION);
                } catch ( Exception ex ) {
                    System.out.println(ex);
                }
        }
        }
    
    }
    

